import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple
from decimal import Decimal

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历_毕业去向落实率按院系专业分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历，毕业去向落实率按院系专业分布 - 指标计算函数
    
    ## 指标说明
    计算各院系、专业下毕业生去向落实率，即毕业去向不为'待就业'和'暂不就业'的学生占比。
    指标按照院系和专业分组统计，同时计算院系级别的汇总数据。
    支持按学历筛选（本科毕业生/专科毕业生/硕士研究生/博士研究生）。
    当education为空时，返回所有学历的明细数据。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值：本科毕业生/专科毕业生/硕士研究生/博士研究生
        
    ## 示例
    ### 输入示例1：不指定学历（返回所有学历明细）
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出示例1：所有学历明细
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "type": "by_college_spec",
                "education": "本科毕业生",
                "description": "按院系-专业分布（本科毕业生）",
                "data": [
                    {"college": "计算机学院", "spec": "计算机科学与技术", "ratio": 0.9567, "student_count": 120},
                    {"college": "计算机学院", "spec": "软件工程", "ratio": 0.9231, "student_count": 130}
                ]
            },
            {
                "type": "by_college",
                "education": "本科毕业生",
                "description": "按院系分布（本科毕业生）",
                "data": [
                    {"college": "计算机学院", "ratio": 0.9423, "student_count": 250},
                    {"college": "文学院", "ratio": 0.8765, "student_count": 180}
                ]
            },
            {
                "type": "total",
                "education": "本科毕业生",
                "description": "总体落实率（本科毕业生）",
                "ratio": 0.9123
            },
            {
                "type": "by_college_spec",
                "education": "总体",
                "description": "按院系-专业分布（总体）",
                "data": [
                    {"college": "计算机学院", "spec": "计算机科学与技术", "ratio": 0.9567, "student_count": 120},
                    {"college": "计算机学院", "spec": "软件工程", "ratio": 0.9231, "student_count": 130}
                ]
            },
            {
                "type": "by_college",
                "education": "总体",
                "description": "按院系分布（总体）",
                "data": [
                    {"college": "计算机学院", "ratio": 0.9423, "student_count": 250},
                    {"college": "文学院", "ratio": 0.8765, "student_count": 180}
                ]
            },
            {
                "type": "total",
                "education": "总体",
                "description": "总体落实率",
                "ratio": 0.9123
            }
        ]
    }
    ```
    
    ### 输入示例2：指定学历
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "本科毕业生"
    }
    ```
    
    ### 输出示例2：指定学历数据
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "type": "by_college_spec",
                "education": "本科毕业生",
                "description": "按院系-专业分布（本科毕业生）",
                "data": [
                    {"college": "计算机学院", "spec": "计算机科学与技术", "ratio": 0.9567, "student_count": 120},
                    {"college": "计算机学院", "spec": "软件工程", "ratio": 0.9231, "student_count": 130}
                ]
            },
            {
                "type": "by_college",
                "education": "本科毕业生",
                "description": "按院系分布（本科毕业生）",
                "data": [
                    {"college": "计算机学院", "ratio": 0.9423, "student_count": 250},
                    {"college": "文学院", "ratio": 0.8765, "student_count": 180}
                ]
            },
            {
                "type": "total",
                "education": "本科毕业生",
                "description": "总体落实率（本科毕业生）",
                "ratio": 0.9123
            },
            {
                "type": "by_college_spec",
                "education": "总体",
                "description": "按院系-专业分布（总体）",
                "data": [
                    {"college": "计算机学院", "spec": "计算机科学与技术", "ratio": 0.9567, "student_count": 120},
                    {"college": "计算机学院", "spec": "软件工程", "ratio": 0.9231, "student_count": 130}
                ]
            },
            {
                "type": "by_college",
                "education": "总体",
                "description": "按院系分布（总体）",
                "data": [
                    {"college": "计算机学院", "ratio": 0.9423, "student_count": 250},
                    {"college": "文学院", "ratio": 0.8765, "student_count": 180}
                ]
            },
            {
                "type": "total",
                "education": "总体",
                "description": "总体落实率",
                "ratio": 0.9123
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历，毕业去向落实率按院系专业分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()
        
        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 处理学历参数（空字符串转为None）
        if education == '':
            education = None
        
        # 4. 构建学历筛选条件
        education_filter = ""
        if education:
            valid_educations = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
            if education not in valid_educations:
                raise ValueError(f"学历参数必须为以下值之一: {valid_educations}")
            education_filter = f"AND education = '{education}'"
        
        logger.info(f"学历筛选: {education if education else '不限制，返回所有学历明细'}")
        
        # 5. 定义查询函数：查询指定学历的落实率数据
        def query_settlement_rate(edu_condition: str, group_name: str = None) -> Dict[str, Any]:
            """查询指定学历的落实率数据"""
            student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
            
            # 查询院系级别的落实率（用于排序）
            college_sql = f"""
            SELECT
                college,
                ROUND(
                    (SUM(CASE
                           WHEN grad_dest_merge NOT IN ('待就业', '暂不就业') THEN 1
                           ELSE 0
                         END) / COUNT(*)),
                    4
                ) AS ratio,
                COUNT(*) as student_count
            FROM
                {student_table}
            WHERE
                shard_tb_key = %s
                {edu_condition}
            GROUP BY college
            ORDER BY ratio DESC, college ASC
            """
            college_results = db.fetchall(college_sql, (shard_tb_key,))
            
            # 查询院系-专业级别的落实率
            college_spec_sql = f"""
            SELECT
                s.college,
                s.spec,
                ROUND(
                    (SUM(CASE
                           WHEN s.grad_dest_merge NOT IN ('待就业', '暂不就业') THEN 1
                           ELSE 0
                         END) / COUNT(*)),
                    4
                ) AS ratio,
                COUNT(*) as student_count
            FROM
                {student_table} s
            WHERE
                s.shard_tb_key = %s
                {edu_condition}
            GROUP BY s.college, s.spec
            """
            college_spec_data = db.fetchall(college_spec_sql, (shard_tb_key,))
            
            # 按院系整体排名重新排序专业数据
            college_order = {college['college']: idx for idx, college in enumerate(college_results)}
            
            # 对专业数据进行排序：先按院系整体排名，再按专业落实率降序
            college_spec_results = sorted(
                college_spec_data,
                key=lambda x: (
                    college_order.get(x['college'], 999),
                    -float(x['ratio']),
                    x['spec']
                )
            )
            
            # 查询总体的落实率
            total_sql = f"""
            SELECT
                ROUND(
                    (SUM(CASE
                           WHEN grad_dest_merge NOT IN ('待就业', '暂不就业') THEN 1
                           ELSE 0
                         END) / COUNT(*)),
                    4
                ) AS ratio
            FROM
                {student_table}
            WHERE
                shard_tb_key = %s
                {edu_condition}
            """
            total_result = db.fetchone(total_sql, (shard_tb_key,))
            
            return {
                'college_results': college_results,
                'college_spec_results': college_spec_results,
                'total_result': total_result
            }
        
        # 6. 计算各学历和总体数据
        result = []
        
        # 6.1 如果指定了学历，只返回该学历的数据
        if education:
            logger.info(f"开始计算学历 '{education}' 的毕业去向落实率")
            edu_data = query_settlement_rate(f"AND education = '{education}'", education)
            
            result.append({
                "type": "by_college_spec",
                "education": education,
                "description": f"按院系-专业分布（{education}）",
                "data": [dict(row) for row in edu_data['college_spec_results']] if edu_data['college_spec_results'] else []
            })
            result.append({
                "type": "by_college",
                "education": education,
                "description": f"按院系分布（{education}）",
                "data": [dict(row) for row in edu_data['college_results']] if edu_data['college_results'] else []
            })
            result.append({
                "type": "total",
                "education": education,
                "description": f"总体落实率（{education}）",
                "ratio": float(edu_data['total_result']['ratio']) if edu_data['total_result'] else 0.0
            })
        else:
            # 6.2 如果没有指定学历，返回所有学历的明细数据
            logger.info("开始计算所有学历的毕业去向落实率明细")
            education_list = ["本科毕业生", "专科毕业生", "硕士研究生", "博士研究生"]
            
            for edu in education_list:
                logger.info(f"计算学历 '{edu}' 的毕业去向落实率")
                edu_data = query_settlement_rate(f"AND education = '{edu}'", edu)
                
                # 只添加有数据的学历
                if edu_data['college_results'] or edu_data['college_spec_results']:
                    result.append({
                        "type": "by_college_spec",
                        "education": edu,
                        "description": f"按院系-专业分布（{edu}）",
                        "data": [dict(row) for row in edu_data['college_spec_results']] if edu_data['college_spec_results'] else []
                    })
                    result.append({
                        "type": "by_college",
                        "education": edu,
                        "description": f"按院系分布（{edu}）",
                        "data": [dict(row) for row in edu_data['college_results']] if edu_data['college_results'] else []
                    })
                    result.append({
                        "type": "total",
                        "education": edu,
                        "description": f"总体落实率（{edu}）",
                        "ratio": float(edu_data['total_result']['ratio']) if edu_data['total_result'] else 0.0
                    })
        
        # 6.3 计算总体数据（必须包含）
        logger.info("开始计算总体的毕业去向落实率")
        overall_data = query_settlement_rate("", "总体")
        result.append({
            "type": "by_college_spec",
            "education": "总体",
            "description": "按院系-专业分布（总体）",
            "data": [dict(row) for row in overall_data['college_spec_results']] if overall_data['college_spec_results'] else []
        })
        result.append({
            "type": "by_college",
            "education": "总体",
            "description": "按院系分布（总体）",
            "data": [dict(row) for row in overall_data['college_results']] if overall_data['college_results'] else []
        })
        result.append({
            "type": "total",
            "education": "总体",
            "description": "总体落实率",
            "ratio": float(overall_data['total_result']['ratio']) if overall_data['total_result'] else 0.0
        })
        
        logger.info(f"指标 '各学历，毕业去向落实率按院系专业分布' 计算成功，返回 {len(result)} 条记录")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历，毕业去向落实率按院系专业分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历，毕业去向落实率按院系专业分布",
            "code": 500,
            "error": str(e)
        }